﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DAL.Entities;
using System.Data.SqlClient;

namespace DAL.EntitiesDAL
{
    public class PhieuMuonDAL : BaseDAL, UTL.IBaseDAL
    {
        #region Implement

        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            return ExecuteQuery("sp_SelectAll_Phieumuon");
        }
        public DataTable Select_PhieuMuon(object obj)
        {
            var o = (PhieuMuon)obj;
            var sql = "set dateformat dmy; execute sp_InPhieumuon_Sel '{0}' ";
            sql = string.Format(sql, o.STT_PM);
            return ExecuteQuery(sql);
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            var sql = "sp_Delete_Phieumuon {0}";
            sql = string.Format(sql, id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (PhieuMuon)obj;
            var sql = "set dateformat dmy; execute sp_Insert_Phieumuon '{0}', '{1}', '{2}' '{3}' ";
            sql = string.Format(sql, o.STT_PM, o.NGAYMUON.ToString("dd/MM/yyy"), o.NGAYTRA.ToString("dd/MM/yyyy"), o.MADOCGIA);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (PhieuMuon)obj;
            var sql = "set dateformat dmy; execute sp_Update_Phieumuon '{0}', '{1}', '{2}', '{3}' ";
            sql = string.Format(sql, o.STT_PM, o.NGAYMUON.ToString("dd/MM/yyyy"), o.NGAYTRA.ToString("dd/MM/yyyy"), o.MADOCGIA);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            var sql = @"Select a.STT_PM, b.MADOCGIA, LTRIM(rtrim(b.HODOCGIA)) + ' ' + LTRIM(rtrim(b.TENDOCGIA)) as HOVATEN, b.NGAYSINH, case when b.GIOITINH = 1  then N'Nữ' else N'Nam' end as GIOITINH, b.DIACHI, b.SCMND, b.DIENTHOAI, b.NGHENGHIEP, b.NOICONGTAC, a.NGAYMUON, a.NGAYTRA
                        from tb_Phieumuon a join tb_Thedocgia b on a.MADOCGIA=b.MADOCGIA";
            sql = string.Format(sql, name);
            return ExecuteQuery(sql);
        }

        public DataTable Search(DAL.Entities.PhieuMuon o)
        {
            string sql;
            if (o.STT_PM.ToString() != "")
                sql = @"Select a.STT_PM, b.MADOCGIA, LTRIM(rtrim(b.HODOCGIA)) + ' ' + LTRIM(rtrim(b.TENDOCGIA)) as HOVATEN, b.NGAYSINH, case when b.GIOITINH = 1  then N'Nữ' else N'Nam' end as GIOITINH, b.DIACHI, b.SCMND, b.DIENTHOAI, b.NGHENGHIEP, b.NOICONGTAC, a.NGAYMUON, a.NGAYTRA
                        from tb_Phieumuon a join tb_Thedocgia b on a.MADOCGIA=b.MADOCGIA Where a.STT_PM like '%" + o.STT_PM + "%' ";
            else
                sql = @"Select a.STT_PM, b.MADOCGIA, LTRIM(rtrim(b.HODOCGIA)) + ' ' + LTRIM(rtrim(b.TENDOCGIA)) as HOVATEN, b.NGAYSINH, case when b.GIOITINH = 1  then N'Nữ' else N'Nam' end as GIOITINH, b.DIACHI, b.SCMND, b.DIENTHOAI, b.NGHENGHIEP, b.NOICONGTAC, a.NGAYMUON, a.NGAYTRA
                             from tb_Phieumuon a join tb_Thedocgia b on a.MADOCGIA=b.MADOCGIA Where a.NGAYMUON like N'%" + o.NGAYMUON + "%' or b.MADOCGIA like N'%" + o.MADOCGIA + "%' ";
                
            return ExecuteQuery(sql);
        }


        #endregion

        #region Other

        public DataTable Select_Max()
        {
            var sql = @"Select Max(STT_PM) as Ma From tb_Phieumuon";
            return ExecuteQuery(sql);
        }


        #endregion
    }
}
